
/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.seagate.app.manager;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import com.seagate.util.db.OracleConnectionPool;
import com.seagate.app.bean.SliderBean;
import com.seagate.app.bean.SliderReportBean;
import java.sql.SQLException;
import java.text.DecimalFormat;

/**
 *
 * @author 452530
 */
public class SliderManager {

    public List loadSilderByBin(String bin) {
        DecimalFormat dfm = new java.text.DecimalFormat("0.00");

        List sliderlist = new ArrayList();
//        String sql = "select * from FDB_EPS_BIN A left join FDB_EPS_SLD_ALL B "
//                + "on A.SLIDER_BO=B.SLIDER_SEQ and A.HGA_BO=B.HGA_BO "
//                + "WHERE A.exp_id ='" + bin + "' and HGA_LOT_CMP_DT is null and A.SLIDER_SHIP_DEST = 'Teparuk'  "
//                + "ORDER BY A.SLIDER_PRIORITY DESC,A.FIRST_RECEIPT ASC,A.SLIDER_BO DESC";

        String sql = "  select * from ("
                + "   select * from (select * from ("
                + "select * from FDB_EPS_BIN A  left join (select SLIDER_SEQ ,max(FIRST_RECEIPT) FIRST_RECEIPT ,BIN,sum(TRANSACTION_QUANTITY) TRANSACTION_QUANTITY from (select substr(LOCATOR,13,5) SLIDER_SEQ,FIRST_RECEIPT,BIN,TRANSACTION_QUANTITY from pc_crib_slider_incoming where sub_inventory = 'FDB-STR' )GROUP BY SLIDER_SEQ, BIN) B "
                + "on substr(A.SLD_BO,6,5)=B.SLIDER_SEQ "
                // + "WHERE A.exp_id ='" + bin + "' and HGA_CMP_DT is null and A.SLD_SHIP_DEST = 'Teparuk' and A.HGA_BO_STATUS not like 'Withdrawn'  and A.HGA_BO_STATUS not like 'Inactive' "
                + "WHERE A.exp_id ='" + bin + "' and HGA_CMP_DT is null  and A.HGA_BO_STATUS not like 'Withdrawn'  and A.HGA_BO_STATUS not like 'Inactive' "
                + "ORDER BY A.SLD_BO_PRIORITY DESC,A.SLD_BO DESC"
                + ") AA left join ("
                + "select * from (select exp_id exp_idX ,sysdate-min(SLD_RECEIPT_DT) slider_gap1,max(SLD_RECEIPT_DT)-min(SLD_RECEIPT_DT) slider_gap2   from FDB_EPS_BIN where exp_id='" + bin + "' and HGA_BO_STATUS not like 'Withdrawn'  and HGA_BO_STATUS not like 'Inactive' and CL_HGARETESTBOS is null and BUILDGROUP not like 'FR'  group by exp_id) AX "
                + "left join  (select exp_id exp_id2  ,count(HGA_BO) icphga  from FDB_EPS_BIN where exp_id='" + bin + "' and HGA_BO_STATUS not like 'Withdrawn'  and HGA_BO_STATUS not like 'Inactive' and SLD_RECEIPT_DT is null group by exp_id) BX "
                + "on AX.exp_idX=BX.exp_id2 "
                + ") AB "
                + "on AA.exp_id=AB.exp_idX"
                + ") X1 left join "
                + "(select exp_id exp_id1 ,max(HGA_BO_CREATION_DT) MAX_HGA_BO_CREATION_DT,min(HGA_ACT_ISSUED_DT) MIN_HGA_ACT_ISSUED_DT,count(*) countslider,min(HGA_ACT_ISSUED_DT)-max(HGA_BO_CREATION_DT) hgaq_act_doc,min(HGA_ACT_ISSUED_DT)-max(SLD_RECEIPT_DT) hgaq_act_rcv,sysdate-max(SLD_RECEIPT_DT) hgaq_sys_rcv,sysdate-max(HGA_BO_CREATION_DT) hgaq_sys_doc,max(HGA_BO_CREATION_DT) docready,max(HGA_BO_CREATION_DT)-max(SLD_RECEIPT_DT) docdelay ,sysdate-max(SLD_RECEIPT_DT) docdelay2 from FDB_EPS_BIN where  HGA_BO_STATUS not like 'Withdrawn'  and HGA_BO_STATUS not like 'Inactive' and CL_HGARETESTBOS is null and BUILDGROUP  like 'FP'  group by exp_id) X2 "
                //+ "(select exp_id exp_id1 ,min(HGA_ACT_ISSUED_DT)-max(HGA_BO_CREATION_DT) hgaq_act_doc,min(HGA_ACT_ISSUED_DT)-max(SLD_RECEIPT_DT) hgaq_act_rcv,sysdate-max(SLD_RECEIPT_DT) hgaq_sys_rcv,sysdate-max(HGA_BO_CREATION_DT) hgaq_sys_doc,max(HGA_BO_CREATION_DT)-max(SLD_RECEIPT_DT) docdelay   from FDB_EPS_BIN where  HGA_BO_STATUS not like 'Withdrawn'  and HGA_BO_STATUS not like 'Inactive' and CL_HGARETESTBOS is null and BUILDGROUP not like 'FR'  group by exp_id) X2 "
                + "on X2.exp_id1=X1.exp_id"
                + "                ) thai left join "
                + "(select W1.exp_id,W1.SLD_BO,W1.slider_start_date,"
                + "  case "
                + " when W1.nomaterial >0 then sysdate-min(W1.slider_start_date) over(partition by W1.exp_id) "
                + " when W1.cwnship >0 then sysdate-min(W1.slider_start_date) over(partition by W1.exp_id) "
                + " else MAX(W1.slider_start_date) over(partition by W1.exp_id)-min(W1.slider_start_date) over(partition by W1.exp_id) "
                + " end wafergap "
                //+ "MAX(W1.slider_start_date) over(partition by W1.exp_id)-min(W1.slider_start_date) over(partition by W1.exp_id) as wafergap  "
                + "from (select A.exp_id, A.SLD_BO , A.SLD_MATERIAL_FORM,  A.cwnship,A.nomaterial, "
                + "case "
                
                + "  when   A.BUILDGROUP like 'FR' then null "
                + "  when   A.SLD_MATERIAL_FORM like 'Wafer Build' and a.waf_ship_dt is null then null "
                + "  when   A.BIPG > 0 and A.SLD_MATERIAL_FORM like 'Wafer Build' then a.waf_ship_dt+2.5 "
                + "  when   A.BIPG = 0 and A.SLD_MATERIAL_FORM like 'Wafer Build' then a.waf_ship_dt "
                + "  when   A.SLD_MATERIAL_FORM like 'Bars In Penang' then a.SLD_BO_FIRST_APPROVAL_DT   "
                + "  else null "
                + " END slider_start_date "
                + " from ( "
                + "    select A1.BUILDGROUP,  A1.exp_id, A1.SLD_BO , A1.SLD_MATERIAL_FORM, A1.SLD_BO_FIRST_APPROVAL_DT, A1.waf_ship_dt , "
                + "          (select count(*)   from FDB_EPS_BIN A12 "
                + "          where A12.SLD_MATERIAL_FORM  like 'Bars In Penang' and A12.HGA_BO_STATUS not like 'Withdrawn'  and A12.HGA_BO_STATUS not like 'Inactive' and A12.exp_id ='" + bin + "' and A12.BUILDGROUP not like 'FR'"
                + "          ) BIPG ,"
                + "          (select count(*)   from FDB_EPS_BIN A13 "
                + "          where A13.SLD_MATERIAL_FORM  like 'Wafer Build' and A13.HGA_BO_STATUS not like 'Withdrawn'  and A13.HGA_BO_STATUS not like 'Inactive' and A13.exp_id ='" + bin + "' and A13.BUILDGROUP not like 'FR' and A13.waf_ship_dt is null "
                + "          ) cwnship ,"
                + "          (select count(*)   from FDB_EPS_BIN A13 "
                + "          where A13.SLD_MATERIAL_FORM  is null and A13.HGA_BO_STATUS not like 'Withdrawn'  and A13.HGA_BO_STATUS not like 'Inactive' and A13.exp_id ='" + bin + "' and A13.BUILDGROUP not like 'FR' "
                + "          ) nomaterial "
                + "    from FDB_EPS_BIN A1 "
                + "    where      A1.HGA_BO_STATUS not like 'Withdrawn'  and  A1.HGA_BO_STATUS not like 'Inactive' and A1.exp_id ='" + bin + "'   "
                + "    ) A "
                + "   ) W1 "
                + "  ) png "
                + "  on thai.exp_id=png.exp_id and thai.SLD_BO=png.SLD_BO";
        // System.out.println("sql="+sql);
        Connection conn = null;
        Statement stmt = null;
        ResultSet rset = null;
        try {
            conn = OracleConnectionPool.getConnection();
            stmt = conn.createStatement();
            rset = stmt.executeQuery(sql);
            while (rset.next() == true) {
                SliderBean sb = new SliderBean();
                sb.setBin(rset.getString("exp_id"));
                sb.setHga_act_issue_dt(rset.getString("HGA_ACT_ISSUED_DT"));
                sb.setHga_bo(rset.getString("HGA_BO"));
                sb.setHga_status(rset.getString("HGA_BO_STATUS"));
                sb.setBuildgroup(rset.getString("BUILDGROUP"));
                sb.setSld_material_form(rset.getString("SLD_MATERIAL_FORM"));
                sb.setSlider_start_date(rset.getString("slider_start_date"));
                sb.setWafgap(Double.parseDouble(dfm.format(rset.getDouble("wafergap"))));
                sb.setSld_bo_first_approval_date(rset.getString("SLD_BO_FIRST_APPROVAL_DT"));
                sb.setSld_bo_priority(rset.getString("SLD_BO_PRIORITY"));
                String receivedate = rset.getString("SLD_RECEIPT_DT");
                if (receivedate == null) {
                    receivedate = rset.getString("FIRST_RECEIPT");
                }

//              if("".equalsIgnoreCase(rset.getString("TRANSACTION_QUANTITY"))){
//              sb.setSld_cmp_qty(rset.getString("SLD_CMP_QTY"));
//              }else{
                sb.setSld_cmp_qty(rset.getString("TRANSACTION_QUANTITY"));
//              }

                sb.setWaf_ship_date(rset.getString("WAF_SHIP_DT"));
                if (receivedate != null) {
                    sb.setLocation("STTH");
                } else if (rset.getString("WAF_SHIP_DT") != null) {
                    sb.setLocation("PNG");
                } else if (rset.getString("WAF_SHIP_DT") == null) {
                    sb.setLocation("NRM");
                    if (sb.getBin().startsWith("TST")) {
                        sb.setLocation("STST");
                    }
                }
                sb.setHga_cmp_date(rset.getString("HGA_CMP_DT"));
                sb.setRetest_bo(rset.getString("CL_HGARETESTBOS"));
                sb.setForcast_date(rset.getString("SLD_FCAST_SHIP_DT"));
                sb.setPriority(rset.getString("HGA_PRIORITY"));
                sb.setProductName(rset.getString("PRODUCTFAMILY"));
                sb.setReceive_date(receivedate);
                sb.setShip_date(rset.getString("SLD_SHIP_DT"));
                sb.setSeq(rset.getString("SLD_BO"));
                sb.setWaf_lot(rset.getString("WAF_LOT"));
                sb.setDoc_ready_date(rset.getString("HGA_BO_CREATION_DT"));
                sb.setTsr(rset.getString("CL_TSR_ELECTRIC"));
                sb.setMaterial_form(rset.getString("HGA_MATERIAL_FORM"));
                sb.setSlider_ship_destination(rset.getString("SLD_SHIP_DEST"));

                int countIncomplete = rset.getInt("icphga");
                if (countIncomplete > 0) {
                    sb.setSld_gap("" + dfm.format(rset.getDouble("slider_gap1")));
                    double sld_con=rset.getDouble("slider_gap1")-rset.getDouble("wafergap");
                    if(sld_con<0){
                      sld_con=0;  
                    }
                    sb.setSlider_contribution(Double.parseDouble(dfm.format(sld_con)));
                    sb.setHgaqueue("0");
                    sb.setDocDelay(0);
                } else {
                    sb.setSld_gap("" + dfm.format(rset.getDouble("slider_gap2")));
                    double sld_con=rset.getDouble("slider_gap2")-rset.getDouble("wafergap");
                    if(sld_con<0){
                      sld_con=0;  
                    }
                    sb.setSlider_contribution(Double.parseDouble(dfm.format(sld_con)));
                    if (rset.getString("HGA_BO_CREATION_DT") != null && !("".equalsIgnoreCase(rset.getString("HGA_BO_CREATION_DT")))) {
                        if (rset.getInt("docdelay") > 0) {
                            if (rset.getString("HGA_ACT_ISSUED_DT") != null && !("".equalsIgnoreCase(rset.getString("HGA_ACT_ISSUED_DT")))) {
                                sb.setHgaqueue("" + dfm.format(rset.getDouble("hgaq_act_doc")));

                            } else {
                                sb.setHgaqueue("" + dfm.format(rset.getDouble("hgaq_sys_doc")));

                            }
                            sb.setDocDelay(Double.parseDouble(dfm.format(rset.getDouble("docdelay"))));
                        } else {
                            sb.setDocDelay(0);
                            if (rset.getString("HGA_ACT_ISSUED_DT") != null && !("".equalsIgnoreCase(rset.getString("HGA_ACT_ISSUED_DT")))) {
                                sb.setHgaqueue("" + dfm.format(rset.getDouble("hgaq_act_rcv")));

                            } else {
                                sb.setHgaqueue("" + dfm.format(rset.getDouble("hgaq_sys_rcv")));

                            }
                        }

                    } else {
                        sb.setHgaqueue("0");
                        sb.setDocDelay(Double.parseDouble(dfm.format(rset.getDouble("docdelay2"))));
                    }

                }



                sb.setWaf_exp_code_descr(rset.getString("WAF_EXP_CODE_DESCR"));

                sliderlist.add(sb);

            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rset.close();
                stmt.close();
                conn.close();
            } catch (SQLException ex) {
            }
        }
        return sliderlist;
    }

    public List loadSilderByBinAllSliderBo(String bin) {
        List sliderlist = new ArrayList();
        DecimalFormat dfm = new java.text.DecimalFormat("0.00");
//        String sql = "select * from FDB_EPS_BIN A left join FDB_EPS_SLD_ALL B "
//                + "on A.SLIDER_BO=B.SLIDER_SEQ and A.HGA_BO=B.HGA_BO "
//                + "WHERE A.exp_id ='" + bin + "' and HGA_LOT_CMP_DT is null and A.SLIDER_SHIP_DEST = 'Teparuk'  "
//                + "ORDER BY A.SLIDER_PRIORITY DESC,A.FIRST_RECEIPT ASC,A.SLIDER_BO DESC";

        String sql = "  select * from ("
                + "   select * from (select * from ("
                + "select * from FDB_EPS_BIN A  left join (select SLIDER_SEQ ,max(FIRST_RECEIPT) FIRST_RECEIPT ,BIN,sum(TRANSACTION_QUANTITY) TRANSACTION_QUANTITY from (select substr(LOCATOR,13,5) SLIDER_SEQ,FIRST_RECEIPT,BIN,TRANSACTION_QUANTITY from pc_crib_slider_incoming where sub_inventory = 'FDB-STR' )GROUP BY SLIDER_SEQ, BIN) B "
                + "on substr(A.SLD_BO,6,5)=B.SLIDER_SEQ "
                //+ "WHERE A.exp_id ='" + bin + "' and  A.SLD_SHIP_DEST = 'Teparuk' and A.HGA_BO_STATUS not like 'Withdrawn'  and A.HGA_BO_STATUS not like 'Inactive'   "
                + "WHERE A.exp_id ='" + bin + "'   and A.HGA_BO_STATUS not like 'Withdrawn'  and A.HGA_BO_STATUS not like 'Inactive'   "
                + "ORDER BY A.SLD_BO_PRIORITY DESC,A.SLD_BO DESC"
                + ") AA left join ("
                + "select * from (select exp_id exp_idX ,sysdate-min(SLD_RECEIPT_DT) slider_gap1,max(SLD_RECEIPT_DT)-min(SLD_RECEIPT_DT) slider_gap2   from FDB_EPS_BIN where exp_id='" + bin + "' and HGA_BO_STATUS not like 'Withdrawn'  and HGA_BO_STATUS not like 'Inactive' and CL_HGARETESTBOS is null and BUILDGROUP not like 'FR' group by exp_id) AX "
                + "left join  (select exp_id exp_id2  ,count(HGA_BO) icphga  from FDB_EPS_BIN where exp_id='" + bin + "' and HGA_BO_STATUS not like 'Withdrawn'  and HGA_BO_STATUS not like 'Inactive' and SLD_RECEIPT_DT is null group by exp_id) BX "
                + "on AX.exp_idX=BX.exp_id2 "
                + ") AB "
                + "on AA.exp_id=AB.exp_idX"
                + ") X1 left join "
                + "(select exp_id exp_id1 ,max(HGA_BO_CREATION_DT) MAX_HGA_BO_CREATION_DT,min(HGA_ACT_ISSUED_DT) MIN_HGA_ACT_ISSUED_DT,count(*) countslider,min(HGA_ACT_ISSUED_DT)-max(HGA_BO_CREATION_DT) hgaq_act_doc,min(HGA_ACT_ISSUED_DT)-max(SLD_RECEIPT_DT) hgaq_act_rcv,sysdate-max(SLD_RECEIPT_DT) hgaq_sys_rcv,sysdate-max(HGA_BO_CREATION_DT) hgaq_sys_doc,max(HGA_BO_CREATION_DT) docready,max(HGA_BO_CREATION_DT)-max(SLD_RECEIPT_DT) docdelay ,sysdate-max(SLD_RECEIPT_DT) docdelay2 from FDB_EPS_BIN where  HGA_BO_STATUS not like 'Withdrawn'  and HGA_BO_STATUS not like 'Inactive' and CL_HGARETESTBOS is null and BUILDGROUP  like 'FP'  group by exp_id) X2 "
                //+ "(select exp_id exp_id1 ,min(HGA_ACT_ISSUED_DT)-max(HGA_BO_CREATION_DT) hgaq_act_doc,min(HGA_ACT_ISSUED_DT)-max(SLD_RECEIPT_DT) hgaq_act_rcv,sysdate-max(SLD_RECEIPT_DT) hgaq_sys_rcv,sysdate-max(HGA_BO_CREATION_DT) hgaq_sys_doc,max(HGA_BO_CREATION_DT)-max(SLD_RECEIPT_DT) docdelay   from FDB_EPS_BIN where  HGA_BO_STATUS not like 'Withdrawn'  and HGA_BO_STATUS not like 'Inactive' and CL_HGARETESTBOS is null and BUILDGROUP not like 'FR'  group by exp_id) X2 "
                + "on X2.exp_id1=X1.exp_id"
                + "                ) thai left join "
                + "(select W1.exp_id,W1.SLD_BO,W1.slider_start_date,"
                + "  case "
                + " when W1.nomaterial >0 then sysdate-min(W1.slider_start_date) over(partition by W1.exp_id) "
                + " when W1.cwnship >0 then sysdate-min(W1.slider_start_date) over(partition by W1.exp_id) "
                + " else MAX(W1.slider_start_date) over(partition by W1.exp_id)-min(W1.slider_start_date) over(partition by W1.exp_id) "
                + " end wafergap "
                //+ "MAX(W1.slider_start_date) over(partition by W1.exp_id)-min(W1.slider_start_date) over(partition by W1.exp_id) as wafergap  "
                + "from (select A.exp_id, A.SLD_BO , A.SLD_MATERIAL_FORM,  A.cwnship,A.nomaterial, "
                + "case "
                 
                + "  when   A.BUILDGROUP like 'FR' then null "
                + "  when   A.SLD_MATERIAL_FORM like 'Wafer Build' and a.waf_ship_dt is null then null "
                + "  when   A.BIPG > 0 and A.SLD_MATERIAL_FORM like 'Wafer Build' then a.waf_ship_dt+2.5 "
                + "  when   A.BIPG = 0 and A.SLD_MATERIAL_FORM like 'Wafer Build' then a.waf_ship_dt "
                + "  when   A.SLD_MATERIAL_FORM like 'Bars In Penang' then a.SLD_BO_FIRST_APPROVAL_DT   "
                + "  else null "
                + " END slider_start_date "
                + " from ( "
                + "    select A1.BUILDGROUP,  A1.exp_id, A1.SLD_BO , A1.SLD_MATERIAL_FORM, A1.SLD_BO_FIRST_APPROVAL_DT, A1.waf_ship_dt , "
                + "          (select count(*)   from FDB_EPS_BIN A12 "
                + "          where A12.SLD_MATERIAL_FORM  like 'Bars In Penang' and A12.HGA_BO_STATUS not like 'Withdrawn'  and A12.HGA_BO_STATUS not like 'Inactive' and A12.exp_id ='" + bin + "' and A12.BUILDGROUP not like 'FR'"
                + "          ) BIPG ,"
                + "          (select count(*)   from FDB_EPS_BIN A13 "
                + "          where A13.SLD_MATERIAL_FORM  like 'Wafer Build' and A13.HGA_BO_STATUS not like 'Withdrawn'  and A13.HGA_BO_STATUS not like 'Inactive' and A13.exp_id ='" + bin + "' and A13.BUILDGROUP not like 'FR' and A13.waf_ship_dt is null "
                + "          ) cwnship, "
                + "          (select count(*)   from FDB_EPS_BIN A13 "
                + "          where A13.SLD_MATERIAL_FORM  is null and A13.HGA_BO_STATUS not like 'Withdrawn'  and A13.HGA_BO_STATUS not like 'Inactive' and A13.exp_id ='" + bin + "' and A13.BUILDGROUP not like 'FR' "
                + "          ) nomaterial "
                + "    from FDB_EPS_BIN A1 "
                + "    where      A1.HGA_BO_STATUS not like 'Withdrawn'  and  A1.HGA_BO_STATUS not like 'Inactive' and A1.exp_id ='" + bin + "'   "
                + "    ) A "
                + "   ) W1 "
                + "  ) png "
                + "  on thai.exp_id=png.exp_id and thai.SLD_BO=png.SLD_BO";
        //  System.out.println("sql="+sql);
        Connection conn = null;
        Statement stmt = null;
        ResultSet rset = null;
        try {
            conn = OracleConnectionPool.getConnection();
            stmt = conn.createStatement();
            rset = stmt.executeQuery(sql);
            while (rset.next() == true) {
                SliderBean sb = new SliderBean();
                sb.setBin(rset.getString("exp_id"));
                sb.setHga_act_issue_dt(rset.getString("HGA_ACT_ISSUED_DT"));
                sb.setHga_bo(rset.getString("HGA_BO"));
                sb.setBuildgroup(rset.getString("BUILDGROUP"));
                sb.setSld_material_form(rset.getString("SLD_MATERIAL_FORM"));
                sb.setSlider_start_date(rset.getString("slider_start_date"));
                sb.setWafgap(Double.parseDouble(dfm.format(rset.getDouble("wafergap"))));
                sb.setSld_bo_first_approval_date(rset.getString("SLD_BO_FIRST_APPROVAL_DT"));
                sb.setSld_bo_priority(rset.getString("SLD_BO_PRIORITY"));
                sb.setHga_status(rset.getString("HGA_BO_STATUS"));
                String receivedate = rset.getString("SLD_RECEIPT_DT");
                if (receivedate == null) {
                    receivedate = rset.getString("FIRST_RECEIPT");
                }
//              if("".equalsIgnoreCase(rset.getString("TRANSACTION_QUANTITY"))){
//              sb.setSld_cmp_qty(rset.getString("SLD_CMP_QTY"));
//              }else{
                sb.setSld_cmp_qty(rset.getString("TRANSACTION_QUANTITY"));
//              }
                sb.setWaf_ship_date(rset.getString("WAF_SHIP_DT"));
                if (receivedate != null) {
                    sb.setLocation("STTH");
                } else if (rset.getString("WAF_SHIP_DT") != null) {
                    sb.setLocation("PNG");
                } else if (rset.getString("WAF_SHIP_DT") == null) {
                    sb.setLocation("NRM");
                    if (sb.getBin().startsWith("TST")) {
                        sb.setLocation("STST");
                    }
                }
                sb.setHga_cmp_date(rset.getString("HGA_CMP_DT"));
                sb.setRetest_bo(rset.getString("CL_HGARETESTBOS"));
                sb.setForcast_date(rset.getString("SLD_FCAST_SHIP_DT"));
                sb.setPriority(rset.getString("HGA_PRIORITY"));
                sb.setProductName(rset.getString("PRODUCTFAMILY"));
                sb.setReceive_date(receivedate);
                sb.setShip_date(rset.getString("SLD_SHIP_DT"));
                sb.setSeq(rset.getString("SLD_BO"));
                sb.setWaf_lot(rset.getString("WAF_LOT"));
                sb.setDoc_ready_date(rset.getString("HGA_BO_CREATION_DT"));
                sb.setTsr(rset.getString("CL_TSR_ELECTRIC"));
                sb.setMaterial_form(rset.getString("HGA_MATERIAL_FORM"));
                sb.setSlider_ship_destination(rset.getString("SLD_SHIP_DEST"));
                int countIncomplete = rset.getInt("icphga");
                if (countIncomplete > 0) {
                    sb.setSld_gap("" + dfm.format(rset.getDouble("slider_gap1")));
                    double sld_con=rset.getDouble("slider_gap1")-rset.getDouble("wafergap");
                    if(sld_con<0){
                      sld_con=0;  
                    }
                    sb.setSlider_contribution(Double.parseDouble(dfm.format(sld_con)));
                    sb.setHgaqueue("0");
                    sb.setDocDelay(0);
                } else {
                    sb.setSld_gap("" + dfm.format(rset.getDouble("slider_gap2")));
                    double sld_con=rset.getDouble("slider_gap2")-rset.getDouble("wafergap");
                    if(sld_con<0){
                      sld_con=0;  
                    }
                    sb.setSlider_contribution(Double.parseDouble(dfm.format(sld_con)));
                    if (rset.getString("HGA_BO_CREATION_DT") != null && !("".equalsIgnoreCase(rset.getString("HGA_BO_CREATION_DT")))) {
                        if (rset.getInt("docdelay") > 0) {
                            if (rset.getString("HGA_ACT_ISSUED_DT") != null && !("".equalsIgnoreCase(rset.getString("HGA_ACT_ISSUED_DT")))) {
                                sb.setHgaqueue("" + dfm.format(rset.getDouble("hgaq_act_doc")));

                            } else {
                                sb.setHgaqueue("" + dfm.format(rset.getDouble("hgaq_sys_doc")));

                            }
                            sb.setDocDelay(Double.parseDouble(dfm.format(rset.getDouble("docdelay"))));
                        } else {
                            sb.setDocDelay(0);
                            if (rset.getString("HGA_ACT_ISSUED_DT") != null && !("".equalsIgnoreCase(rset.getString("HGA_ACT_ISSUED_DT")))) {
                                sb.setHgaqueue("" + dfm.format(rset.getDouble("hgaq_act_rcv")));

                            } else {
                                sb.setHgaqueue("" + dfm.format(rset.getDouble("hgaq_sys_rcv")));

                            }
                        }

                    } else {
                        sb.setHgaqueue("0");
                        sb.setDocDelay(Double.parseDouble(dfm.format(rset.getDouble("docdelay2"))));
                    }

                }

                sb.setWaf_exp_code_descr(rset.getString("WAF_EXP_CODE_DESCR"));
                sliderlist.add(sb);

            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rset.close();
                stmt.close();
                conn.close();
            } catch (SQLException ex) {
            }
        }
        return sliderlist;
    }

    public List loadIncomingBin() {
        List binlist = new ArrayList();
        String sql = "select BIN  from (select   *  from pc_crib_slider_incoming A ,fdb_eps_bin B where SUBSTR(b.SLD_BO,6,5) = substr(a.locator,instr(a.locator,'/')+4,5)  ) where sub_inventory = 'FDB-STR' and  BIN IS  NOT NULL AND substr(BIN,1,3) in ('TDT','TST','TDM','RTD') GROUP BY BIN ORDER BY BIN";

        Connection conn = null;
        Statement stmt = null;
        ResultSet rset = null;
        try {
            conn = OracleConnectionPool.getConnection();
            stmt = conn.createStatement();
            rset = stmt.executeQuery(sql);
            while (rset.next() == true) {
                binlist.add(rset.getString("BIN"));

            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rset.close();
                stmt.close();
                conn.close();
            } catch (SQLException ex) {
            }
        }
        return binlist;
    }

    public List loadIncomingBinByProduct(String prd) {
        List binlist = new ArrayList();
        String sql = "select BIN  from pc_crib_slider_incoming where  sub_inventory = 'FDB-STR' and  BIN IS  NOT NULL AND substr(BIN,1,3) in (" + prd + ") GROUP BY BIN ORDER BY BIN";

        Connection conn = null;
        Statement stmt = null;
        ResultSet rset = null;
        try {
            conn = OracleConnectionPool.getConnection();
            stmt = conn.createStatement();
            rset = stmt.executeQuery(sql);
            while (rset.next() == true) {
                binlist.add(rset.getString("BIN"));

            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rset.close();
                stmt.close();
                conn.close();
            } catch (SQLException ex) {
            }
        }
        return binlist;
    }

    public List loadForecastBinByProduct(String prd) {
        List binlist = new ArrayList();
        // String sql = "select BIN  from pc_crib_slider_incoming where  BIN IS  NOT NULL AND substr(BIN,1,3) in ("+prd+") GROUP BY BIN ORDER BY BIN";

        String sql = "select * from (select exp_id BIN from FDB_EPS_BIN A  WHERE "
                + "A.sld_act_issued_dt <= (sysdate+7) and A.sld_act_issued_dt >= sysdate and A.BLD_INTENT_MODIFIED_DT >=(sysdate-60) and "//A.HGA_BO_STATUS not like 'Withdrawn' and A.HGA_BO_STATUS not like 'Inactive'  and "
                + "HGA_CMP_DT is null and A.SLD_SHIP_DEST = 'Teparuk'  and substr(A.exp_id,1,3)  in (" + prd + ")  "
                + "group by exp_id ORDER BY A.exp_id DESC ) "
                + "minus "
                + "(select BIN  from pc_crib_slider_incoming  where sub_inventory = 'FDB-STR' and BIN is not null group by BIN) ";

        Connection conn = null;
        Statement stmt = null;
        ResultSet rset = null;
        try {
            conn = OracleConnectionPool.getConnection();
            stmt = conn.createStatement();
            rset = stmt.executeQuery(sql);
            while (rset.next() == true) {
                binlist.add(rset.getString("BIN"));

            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rset.close();
                stmt.close();
                conn.close();
            } catch (SQLException ex) {
            }
        }
        return binlist;
    }

    public List loadForcastBin() {
        List binlist = new ArrayList();

        String sql = "select * from (select exp_id BIN from FDB_EPS_BIN A  WHERE "
                + "A.sld_act_issued_dt <= (sysdate+7) and A.sld_act_issued_dt >= sysdate and A.BLD_INTENT_MODIFIED_DT >=(sysdate-60) and " 
                + "HGA_CMP_DT is null and A.SLD_SHIP_DEST = 'Teparuk' AND substr(exp_id,1,3) in ('TDT','TST','TDM','RTD') "
                + "group by exp_id ORDER BY A.exp_id DESC ) "
                + "minus "
                + "(select BIN  from pc_crib_slider_incoming  where sub_inventory = 'FDB-STR' and BIN is not null group by BIN)";
        Connection conn = null;
        Statement stmt = null;
        ResultSet rset = null;
        try {
            conn = OracleConnectionPool.getConnection();
            stmt = conn.createStatement();
            rset = stmt.executeQuery(sql);
            while (rset.next() == true) {
                binlist.add(rset.getString("BIN"));

            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rset.close();
                stmt.close();
                conn.close();
            } catch (SQLException ex) {
            }
        }
        return binlist;
    }

    public String CheckBin(String bin) {


        String sql = "select hga_bo_status from pc_crib_slider_incoming a "
                + "join FDB_EPS_BIN b "
                + "on a.bin='" + bin + "' and a.bin=b.exp_id and substr(locator,INSTR(locator, '/', 1)+4,5)=substr(b.sld_bo,6,5) and B.SLD_SHIP_DEST='Teparuk' where a.sub_inventory = 'FDB-STR'  ";
        Connection conn = null;
        Statement stmt = null;
        ResultSet rset = null;

        try {
            conn = OracleConnectionPool.getConnection();
            stmt = conn.createStatement();
            rset = stmt.executeQuery(sql);
            while (rset.next() == true) {
                return rset.getString("hga_bo_status");

            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rset.close();
                stmt.close();
                conn.close();
            } catch (SQLException ex) {
            }
        }
        return "";
    }

    public List IncomingBinReport(String product) {
        List binlist = new ArrayList();

        String sql = "select * from ( "
                + "select * from (select exp_id exp_id1 ,max(PRODUCTFAMILY) PRODUCTFAMILY ,max(HGA_PRIORITY) HGA_PRIORITY,sysdate-min(SLD_RECEIPT_DT) slider_gap1,max(SLD_RECEIPT_DT)-min(SLD_RECEIPT_DT) slider_gap2   from FDB_EPS_BIN where   HGA_BO_STATUS not like 'Withdrawn'  and HGA_BO_STATUS not like 'Inactive' and CL_HGARETESTBOS is null and BUILDGROUP not like 'FR' group by exp_id) AX  "
                + "left join  (select exp_id exp_id2  ,count(*) icphga  from FDB_EPS_BIN where  HGA_BO_STATUS not like 'Withdrawn'  and HGA_BO_STATUS not like 'Inactive' and SLD_RECEIPT_DT is null group by exp_id) BX  "
                + "on AX.exp_id1=BX.exp_id2  "
                + ") X1 left join "
                + "(select exp_id exp_id1 ,max(HGA_BO_CREATION_DT) HGA_BO_CREATION_DT,min(HGA_ACT_ISSUED_DT) HGA_ACT_ISSUED_DT,count(*) countslider,min(HGA_ACT_ISSUED_DT)-max(HGA_BO_CREATION_DT) hgaq_act_doc,min(HGA_ACT_ISSUED_DT)-max(SLD_RECEIPT_DT) hgaq_act_rcv,sysdate-max(SLD_RECEIPT_DT) hgaq_sys_rcv,sysdate-max(HGA_BO_CREATION_DT) hgaq_sys_doc,max(HGA_BO_CREATION_DT) docready,max(HGA_BO_CREATION_DT)-max(SLD_RECEIPT_DT) docdelay ,sysdate-max(SLD_RECEIPT_DT) docdelay2 from FDB_EPS_BIN where  HGA_BO_STATUS not like 'Withdrawn'  and HGA_BO_STATUS not like 'Inactive' and CL_HGARETESTBOS is null and BUILDGROUP   like 'FP'  group by exp_id) X2  "
                + "on X2.exp_id1=X1.exp_id1 "
                + "where X1.exp_id1 in (select BIN  from  pc_crib_slider_incoming where sub_inventory = 'FDB-STR' and bin is not null and substr(bin,1,3) in ('" + product + "') GROUP BY  BIN) order by X1.HGA_PRIORITY DESC  ";
//                + ""
//                + "
//                + "A.SLD_FCAST_SHIP_DT <= (sysdate+2) and A.SLD_FCAST_SHIP_DT >= sysdate and A.BLD_INTENT_MODIFIED_DT >=(sysdate-60) and A.HGA_BO_STATUS not like 'Withdrawn' and A.HGA_BO_STATUS not like 'Inactive'  and "
//                + "HGA_CMP_DT is null and A.SLD_SHIP_DEST = 'Teparuk' "
//                + "group by exp_id ORDER BY A.exp_id DESC ) "
//                + "minus "
//                + "(select BIN  from pc_crib_slider_incoming  where BIN is not null group by BIN)";
        Connection conn = null;
        Statement stmt = null;
        ResultSet rset = null;
        try {
            conn = OracleConnectionPool.getConnection();
            stmt = conn.createStatement();
            rset = stmt.executeQuery(sql);
            while (rset.next() == true) {
                SliderReportBean rp = new SliderReportBean();
                rp.setBinId(rset.getString("exp_id1"));
                rp.setProductfamily(rset.getString("PRODUCTFAMILY"));
                rp.setHgaPriority(rset.getInt("HGA_PRIORITY"));
                int countIncomplete = rset.getInt("icphga");
                if (countIncomplete > 0) {
                    rp.setSliderGap(rset.getDouble("slider_gap1"));
                    rp.setHgaQueue(0);
                    rp.setDocDelay(0);

                } else {
                    rp.setSliderGap(rset.getDouble("slider_gap2"));

                    if (rset.getString("HGA_BO_CREATION_DT") != null && !("".equalsIgnoreCase(rset.getString("HGA_BO_CREATION_DT")))) {
                        if (rset.getInt("docdelay") > 0) {
                            if (rset.getString("HGA_ACT_ISSUED_DT") != null && !("".equalsIgnoreCase(rset.getString("HGA_ACT_ISSUED_DT")))) {
                                rp.setHgaQueue(rset.getDouble("hgaq_act_doc"));

                            } else {
                                rp.setHgaQueue(rset.getDouble("hgaq_sys_doc"));

                            }
                            rp.setDocDelay(rset.getDouble("docdelay"));
                        } else {
                            rp.setDocDelay(0);
                            if (rset.getString("HGA_ACT_ISSUED_DT") != null && !("".equalsIgnoreCase(rset.getString("HGA_ACT_ISSUED_DT")))) {

                                rp.setHgaQueue(rset.getDouble("hgaq_act_rcv"));
                            } else {
                                rp.setHgaQueue(rset.getDouble("hgaq_sys_rcv"));

                            }
                        }

                    } else {
                        rp.setHgaQueue(0);
                        rp.setDocDelay(rset.getDouble("docdelay2"));
                    }

                }
                binlist.add(rp);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rset.close();
                stmt.close();
                conn.close();
            } catch (SQLException ex) {
            }
        }
        return binlist;
    }
     public List HistoryBinReport(String product) {
        List binlist = new ArrayList();

        String sql = "select * from ( "
                + "select * from (select exp_id exp_id1 ,max(PRODUCTFAMILY) PRODUCTFAMILY ,max(HGA_PRIORITY) HGA_PRIORITY,sysdate-min(SLD_RECEIPT_DT) slider_gap1,max(SLD_RECEIPT_DT)-min(SLD_RECEIPT_DT) slider_gap2   from FDB_EPS_BIN where /*exp_id='RTD-00975' and*/ HGA_BO_STATUS not like 'Withdrawn'  and HGA_BO_STATUS not like 'Inactive' and CL_HGARETESTBOS is null and BUILDGROUP not like 'FR' group by exp_id) AX  "
                + "left join  (select exp_id exp_id2  ,count(*) icphga  from FDB_EPS_BIN where  HGA_BO_STATUS not like 'Withdrawn'  and HGA_BO_STATUS not like 'Inactive' and SLD_RECEIPT_DT is null group by exp_id) BX  "
                + "on AX.exp_id1=BX.exp_id2  "
                + ") X1 left join "
                + "(select exp_id exp_id1 ,max(HGA_BO_CREATION_DT) HGA_BO_CREATION_DT,min(HGA_ACT_ISSUED_DT) HGA_ACT_ISSUED_DT,count(*) countslider,min(HGA_ACT_ISSUED_DT)-max(HGA_BO_CREATION_DT) hgaq_act_doc,min(HGA_ACT_ISSUED_DT)-max(SLD_RECEIPT_DT) hgaq_act_rcv,sysdate-max(SLD_RECEIPT_DT) hgaq_sys_rcv,sysdate-max(HGA_BO_CREATION_DT) hgaq_sys_doc,max(HGA_BO_CREATION_DT) docready,max(HGA_BO_CREATION_DT)-max(SLD_RECEIPT_DT) docdelay ,sysdate-max(SLD_RECEIPT_DT) docdelay2 from FDB_EPS_BIN where  HGA_BO_STATUS not like 'Withdrawn'  and HGA_BO_STATUS not like 'Inactive' and CL_HGARETESTBOS is null and BUILDGROUP   like 'FP'  group by exp_id) X2  "
                + "on X2.exp_id1=X1.exp_id1 "
                + "where X1.exp_id1 in (select BIN  from  pc_crib_slider_incoming where sub_inventory = 'FDB-STR' and bin is not null and substr(bin,1,3) in ('" + product + "') GROUP BY  BIN) order by X1.HGA_PRIORITY DESC  ";
//                + ""
//                + "
//                + "A.SLD_FCAST_SHIP_DT <= (sysdate+2) and A.SLD_FCAST_SHIP_DT >= sysdate and A.BLD_INTENT_MODIFIED_DT >=(sysdate-60) and A.HGA_BO_STATUS not like 'Withdrawn' and A.HGA_BO_STATUS not like 'Inactive'  and "
//                + "HGA_CMP_DT is null and A.SLD_SHIP_DEST = 'Teparuk' "
//                + "group by exp_id ORDER BY A.exp_id DESC ) "
//                + "minus "
//                + "(select BIN  from pc_crib_slider_incoming  where BIN is not null group by BIN)";
        Connection conn = null;
        Statement stmt = null;
        ResultSet rset = null;
        try {
            conn = OracleConnectionPool.getConnection();
            stmt = conn.createStatement();
            rset = stmt.executeQuery(sql);
            while (rset.next() == true) {
                SliderReportBean rp = new SliderReportBean();
                rp.setBinId(rset.getString("exp_id1"));
                rp.setProductfamily(rset.getString("PRODUCTFAMILY"));
                rp.setHgaPriority(rset.getInt("HGA_PRIORITY"));
                int countIncomplete = rset.getInt("icphga");
                if (countIncomplete > 0) {
                    rp.setSliderGap(rset.getDouble("slider_gap1"));
                    rp.setHgaQueue(0);
                    rp.setDocDelay(0);

                } else {
                    rp.setSliderGap(rset.getDouble("slider_gap2"));

                    if (rset.getString("HGA_BO_CREATION_DT") != null && !("".equalsIgnoreCase(rset.getString("HGA_BO_CREATION_DT")))) {
                        if (rset.getInt("docdelay") > 0) {
                            if (rset.getString("HGA_ACT_ISSUED_DT") != null && !("".equalsIgnoreCase(rset.getString("HGA_ACT_ISSUED_DT")))) {
                                rp.setHgaQueue(rset.getDouble("hgaq_act_doc"));

                            } else {
                                rp.setHgaQueue(rset.getDouble("hgaq_sys_doc"));

                            }
                            rp.setDocDelay(rset.getDouble("docdelay"));
                        } else {
                            rp.setDocDelay(0);
                            if (rset.getString("HGA_ACT_ISSUED_DT") != null && !("".equalsIgnoreCase(rset.getString("HGA_ACT_ISSUED_DT")))) {

                                rp.setHgaQueue(rset.getDouble("hgaq_act_rcv"));
                            } else {
                                rp.setHgaQueue(rset.getDouble("hgaq_sys_rcv"));

                            }
                        }

                    } else {
                        rp.setHgaQueue(0);
                        rp.setDocDelay(rset.getDouble("docdelay2"));
                    }

                }
                binlist.add(rp);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rset.close();
                stmt.close();
                conn.close();
            } catch (SQLException ex) {
            }
        }
        return binlist;
    }
}
